<?php
if (!defined("YUEXUAN_REAL_ROOT_PATH")) {
    exit ("Access Denied.");
}

class flowException extends Exception
{
    // pass
}

/**
 * 资金流
 *
 * @author tongjun
 *
 */
class tally_flow extends base
{
    public static $INOUT_INCOMME = 3;
    public static $INOUT_EXPENSE = 4;
    public static $INOUT_TRANSFER = 2;
    public static $INOUT_DESC = array(
        2 => "转账",
        3 => "收入",
        4 => "支出"
    );
    private $flowTableName = "#tablePre#moneyFlow";

    // 按用户分页读取资金流动记录
    public function findFlowByUserId($userId, $accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $orderField, $start = 0, $limit = 20, $inOrOut = "")
    {
        $where = $this->createQueryWhere($accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $inOrOut);
        $order = " f.tradeTime desc, f.id desc";
        if ($orderField) {
            switch ($orderField) {
                case "amountDesc" :
                    $order = "f.money desc";
                    break;
                case "amountAsc" :
                    $order = "f.money asc";
                    break;
                case "tradeTimeDesc" :
                    $order = "f.tradeTime desc";
                    break;
                case "tradeTimeAsc" :
                    $order = "f.tradeTime asc";
                    break;
            }
        }
        $limitStr = "";
        if ($limit > 0) {
            $limitStr = "limit {$start}, {$limit}";
        }
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "select * from (select f.*, (f.moneyIn+f.moneyOut) as money from {$this->flowTableName} f " . "left join {$tagMappingTableName} m on m.flowId=f.id " . "where userId={$userId} {$where} " . "group by f.id) as f order by {$order}, f.id desc " . "{$limitStr}";
        return $this->db(ext_dbHandle::$slaveDbServer)->getRows($sql);
    }

    public function inCountByUserId($userId, $accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $orderField, $inOrOut = "")
    {
        if ($inOrOut && $inOrOut !== tally_flow::$INOUT_INCOMME) {
            return 0;
        }
        return $this->flowCountByUserId($userId, $accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $orderField, 'moneyIn');
    }

    public function outCountByUserId($userId, $accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $orderField, $inOrOut = "")
    {
        if ($inOrOut && $inOrOut !== tally_flow::$INOUT_EXPENSE) {
            return 0;
        }
        return $this->flowCountByUserId($userId, $accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $orderField, 'moneyOut');
    }

    // 按用户读取流入流出统计
    public function flowCountByUserId($userId, $accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $orderField, $flowDirect = "moneyIn")
    {
        $where = $this->createQueryWhere($accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount);
        $order = " f.tradeTime desc, f.id asc";
        if ($orderField) {
            switch ($orderField) {
                case "amountDesc" :
                    $order = "f.money desc";
                    break;
                case "amountAsc" :
                    $order = "f.money asc";
                    break;
                case "tradeTimeDesc" :
                    $order = "f.tradeTime desc";
                    break;

                case "tradeAsc" :
                    $order = "f.tradeTime asc";
                    break;
            }
        }
        $tagMappingTableName = tally_tag::$tagMappingTableName;

        $sql = "select sum(money) as s from(select * from (select f.*, f." . $flowDirect . " as money from {$this->flowTableName} f "
            . "left join {$tagMappingTableName} m on m.flowId=f.id "
            . "where userId={$userId} {$where} "
            . "group by f.id) as f order by {$order}, f.id desc " . ") as table1";
        $row = $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
        return $row["s"];
    }

    // 按用户统计资金流动记录数
    public function countFlowByUserId($userId, $accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount = 0, $maxAmount = 0, $inOrOut = "")
    {
        $where = $this->createQueryWhere($accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $inOrOut);
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "select count(*) as c from {$this->flowTableName} f "
            . "left join {$tagMappingTableName} m on m.flowId=f.id "
            . "where userId={$userId} {$where}";
        $row = $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
        return $row ["c"];
    }

    private function createQueryWhere($accountId, $categoryId, $dateFrom, $dateTo, $tagIds, $remark, $minAmount, $maxAmount, $inOrOut = "")
    {
        $tagIds = trim($tagIds, ",");
        $where = "";
        if ($accountId) {
            $where .= " and f.accountId={$accountId} ";
        }
        if ($dateFrom) {
            $dateFrom = date("Y-m-d H:i:s", strtotime($dateFrom));
            $where .= " and f.tradeTime>='$dateFrom'";
        }
        if ($dateTo) {
            $dateTo = date("Y-m-d H:i:s", strtotime($dateTo) + 24 * 60 * 60 - 1);
            $where .= " and f.tradeTime<='$dateTo'";
        }
        if ($tagIds) {
            if (strpos($tagIds, ",") != false) {
                $where .= " and m.tagId in ($tagIds)";
            } else {
                $where .= " and m.tagId=$tagIds";
            }
        }
        if ($remark) {
            $where .= " and f.desc like '%{$remark}%'";
        }
        if ($minAmount) {
            $where .= " and (f.moneyIn >= {$minAmount} and f.moneyOut = 0 or f.moneyIn = 0 and f.moneyOut >= {$minAmount})";
        }
        if ($maxAmount) {
            $where .= " and (f.moneyIn <= {$maxAmount} and f.moneyOut = 0 or f.moneyIn = 0 and f.moneyOut <= {$maxAmount})";
        }
        if ($inOrOut) {
            $where .= " and f.inOrOut={$inOrOut}";
        }
        if ($categoryId) {
            $categoryIdArray = explode(",", $categoryId);
            foreach ($categoryIdArray as $k => $v) {
                if (!$v) {
                    unset ($categoryIdArray [$k]);
                }
            }
            $categoryId = join(",", $categoryIdArray);
            if ($categoryId) {
                $where .= " and f.flowType in ({$categoryId})";
            }
        }

        return $where;
    }

    // 按账户分页读取资金流动记录
    public function findFlowByAccountId($userId, $accountId, $start = 0, $limit = 20)
    {
        $sql = "select * from {$this->flowTableName} " . "where userId={$userId} and accountId={$accountId} " . "order by tradeTime desc " . "limit {$start}, {$limit}";
        return $this->db(ext_dbHandle::$slaveDbServer)->getRows($sql);
    }

    // 按账户统计资金流动记录数
    public function countFlowByAccountId($userId, $accountId)
    {
        $sql = "select count(1) as c from {$this->flowTableName} " . "where userId={$userId} and accountId={$accountId}";
        $row = $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
        return $row ["c"];
    }

    // 读取资金流动记录的tag信息
    public function findTagByFlow($userId, $flowId)
    {
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "select m.* from {$this->flowTableName} f " . "right join {$tagMappingTableName} m on f.id=m.flowId " . "where f.userId={$userId} and f.id={$flowId}";
        return $this->db(ext_dbHandle::$slaveDbServer)->getRows($sql);
    }

    // 根据账号id读取资金流动记录
    public function findFlowById($userId, $flowId)
    {
        $sql = "select * from {$this->flowTableName} " . "where userId={$userId} and id={$flowId}";
        return $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
    }

    // 根据flowId读取转账记录
    public function findFlowsByTransferFlowId($userId, $transferFlowId)
    {
        $sql = "select * from {$this->flowTableName} " . "where userId={$userId} and transferFlowId={$transferFlowId}";
        return $this->db(ext_dbHandle::$slaveDbServer)->getRows($sql);
    }

    /**
     * 创建资金流动记录
     * @param $userId
     * @param $accountId
     * @param $time
     * @param $inout
     * @param $subjectId1
     * @param $moneyIn
     * @param $moneyOut
     * @param array $tagIds
     * @param string $desc
     * @param int $caakeeId
     * @throws Exception
     * @throws flowException
     */
    public function createFlow($userId, $accountId, $time, $inout, $subjectId1, $moneyIn, $moneyOut, $tagIds = array(), $desc = "", $caakeeId = 0)
    {
        $sql = "select * from {$this->flowTableName} " . "where userId={$userId} and caakeeId={$caakeeId}";
        if ($caakeeId && $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql)) {
            return;
        }

        $defaultFlowInfo = array();
        $accountClass = new tally_account();
        // get last flow info
        $flowInfo = array(
            "userId" => $userId,
            "accountId" => $accountId,
            "tradeTime" => $time,
            "inOrOut" => $inout,
            "flowType" => $subjectId1,
            "moneyIn" => $moneyIn,
            "moneyOut" => $moneyOut,
            "desc" => $desc,
            "caakeeId" => $caakeeId
//				"remain" => $lastFlowInfo["remain"]+$money
        );
        if ($inout == tally_flow::$INOUT_TRANSFER) {
            if ($subjectId1 == $accountId) {
                throw new flowException("转出账号与转入账号不能相同");
            }
            unset ($flowInfo ["flowType"]);
            $flowInfo ["transferTargetAccountId"] = $subjectId1;
        }
        $flowInfo = array_merge($defaultFlowInfo, $flowInfo);
        $db = $this->db(ext_dbHandle::$masterDbServer);
        $db->begin();
        try {
            $db->insert($this->flowTableName, $flowInfo);
            $flowId = $db->insert_id();
//            var_dump($accountId);
//            var_dump($subjectId1);
            if ($tagIds && count($tagIds) > 0) {
                $tagClass = new tally_tag ();
                foreach ($tagIds as $tagId) {
                    if ($tagId) {
                        $tag = $tagClass->findTagById($userId, $tagId);
                        if (!$tag) {
                            continue;
                        }
                        $tagMappingInfo = array(
                            "flowId" => $flowId,
                            "tagId" => $tagId
                        );
                        $db->insert(tally_tag::$tagMappingTableName, $tagMappingInfo);
                    }
                }
            }

            // 更新账户余额
            $accountClass->updateAccountCurrentValue($userId, $accountId, $moneyIn, $moneyOut);
            // 更新交易记录中的余额
            $flowInfo["id"] = $flowId;
            $this->updateFlowRemain($flowInfo, $moneyIn, $moneyOut);

            if ($inout == tally_flow::$INOUT_TRANSFER) {
                $transferTargetFlowInfo = array_merge($flowInfo, array(
                    "accountId" => $subjectId1,
                    "transferTargetAccountId" => $accountId,
                    "transferFlowId" => $flowId,
                    "moneyIn" => $moneyOut,
                    "moneyOut" => $moneyIn,
                ));
                unset($transferTargetFlowInfo["id"]);
                $db->insert($this->flowTableName, $transferTargetFlowInfo);
                $transferTargetFlowId = $db->insert_id();

                // 更新账户余额
                $accountClass->updateAccountCurrentValue($userId, $subjectId1, $moneyOut, $moneyIn);
                // 更新交易记录中的余额
                $transferTargetFlowInfo["id"] = $transferTargetFlowId;
                $this->updateFlowRemain($transferTargetFlowInfo, $moneyOut, $moneyIn);

                $db->update($this->flowTableName, array(
                    "transferFlowId" => $flowId
                ), array(
                    "id" => $flowId
                ));
            }

            $db->commit();
        } catch (Exception $e) {
            $db->rollback();
            var_dump($e);
            throw $e;
        }
    }

    // 更新交易记录中的余额
    public function updateFlowRemain($flowInfo, $moneyIn, $moneyOut)
    {
        $moneyChange = $moneyIn - $moneyOut;
        $sql = "update {$this->flowTableName} set remain=remain+{$moneyChange} where userId={$flowInfo["userId"]} and accountId={$flowInfo["accountId"]} and (tradeTime>'{$flowInfo["tradeTime"]}' or (tradeTime='{$flowInfo["tradeTime"]}' and id>{$flowInfo["id"]}))";
        $this->db(ext_dbHandle::$masterDbServer)->query($sql);

        $sql = "select sum(moneyIn) as sumIn, sum(moneyOut) as sumOut from {$this->flowTableName} where userId={$flowInfo["userId"]} and accountId={$flowInfo["accountId"]} and (tradeTime<'{$flowInfo["tradeTime"]}' or (tradeTime='{$flowInfo["tradeTime"]}' and id<={$flowInfo["id"]}))";
        $rs = $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
        $remain = $rs['sumIn'] - $rs['sumOut'];
//        echo $flowInfo["id"]. ' '.$remain.'<br>';
        $sql = "update {$this->flowTableName} set remain={$remain} where id={$flowInfo["id"]}";
        $this->db(ext_dbHandle::$masterDbServer)->query($sql);
    }

    public function updateFlowInfo($userId, $flowId, $flowInfo, $tags)
    {
        $oldFlowInfo = $this->findFlowById($userId, $flowId);
        if (!$oldFlowInfo) {
            exit('记录未找到');
        }
        $flowInfo = array_merge($oldFlowInfo, $flowInfo);
        $this->removeFlow($userId, $flowId);

        $subjectId1 = $flowInfo["flowType"];
        if ($oldFlowInfo["inOrOut"] == tally_flow::$INOUT_TRANSFER) {
            $subjectId1 = $flowInfo["transferTargetAccountId"];
        }
//        var_dump($oldFlowInfo);
//        var_dump($flowInfo);
        $this->createFlow($userId, $flowInfo["accountId"], $flowInfo["tradeTime"], $oldFlowInfo["inOrOut"],
            $subjectId1, $flowInfo["moneyIn"], $flowInfo["moneyOut"], $tags, $flowInfo["desc"]);
    }

    // 删除资金流动记录
    public function removeFlow($userId, $flowId)
    {
        $flowInfo = $this->findFlowById($userId, $flowId);
        if (!$flowInfo) {
            return;
        }
        $transferTargetAccountId = $flowInfo["transferTargetAccountId"];
        $inout = $flowInfo["inOrOut"];
        $moneyIn = $flowInfo["moneyIn"];
        $moneyOut = $flowInfo["moneyOut"];
        $accountClass = new tally_account();
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "delete from {$tagMappingTableName} " . "where flowId in (select id from {$this->flowTableName} " . "where userId={$userId} and id={$flowId})";
        $db = $this->db(ext_dbHandle::$masterDbServer);
        $db->begin();
        try {
            $db->exc($sql);
            $sql = "delete from {$this->flowTableName} " . "where userId={$userId} and id={$flowId}";
            $db->exc($sql);
            // 更新账户余额
            $accountId = $flowInfo["accountId"];
//            var_dump($flowInfo);
//            echo $flowId.'xxx<br>';
            $accountClass->updateAccountCurrentValue($userId, $accountId, -$moneyIn, -$moneyOut);
            // 更新交易记录中的余额
            $this->updateFlowRemain($flowInfo, -$moneyIn, -$moneyOut);

            if ($inout == tally_flow::$INOUT_TRANSFER) {
                // 转账目标记录
                $rows = $this->findFlowsByTransferFlowId($userId, $flowInfo['transferFlowId']);
                foreach ($rows as $row) {
                    if ($row["id"] != $flowInfo["id"]) {
                        $transferTargetFlowId = $row["id"];
                        $transferTargetFlowInfo = $row;

                        // 更新账户余额
                        $accountClass->updateAccountCurrentValue($userId, $transferTargetAccountId, -$moneyOut, -$moneyIn);
                        $this->updateFlowRemain($transferTargetFlowInfo, -$moneyOut, -$moneyIn);

                        $sql = "delete from {$tagMappingTableName} " . "where flowId in (select id from {$this->flowTableName} " . "where userId={$userId} and id={$transferTargetFlowId})";
                        $db->exc($sql);
                        $sql = "delete from {$this->flowTableName} " . "where userId={$userId} and id={$transferTargetFlowId}";
                        $db->exc($sql);
                    }
                }
            }

            $db->commit();
        } catch (Exception $e) {
            $db->rollback();
            throw $e;
        }
    }

    // 删除账户上的所有资金流动记录
    public function removeFlowByAccountId($userId, $accountId)
    {
        $flowList = $this->findFlowByUserId($userId, $accountId, null, null, null, null, null, 0, 0, null, null, 0, -1);
        if ($flowList) {
            foreach ($flowList as $flow) {
                $this->removeFlow($userId, $flow["id"]);
            }
        }
    }

    // 删除账户上的所有资金流动记录
    public function removeFlowByFlowTypeId($userId, $flowTypeId)
    {
        $flowList = $this->findFlowByUserId($userId, null, $flowTypeId, null, null, null, null, 0, 0, null, 0, -1);
        if ($flowList) {
            foreach ($flowList as $flow) {
                $this->removeFlow($userId, $flow["id"]);
            }
        }
    }

    // 删除用户的所有资金流动记录
    public function removeFlowByUserId($userId)
    {
        $flowList = $this->findFlowByUserId($userId, null, null, null, null, null, null, 0, 0, null, 0, -1);
        if ($flowList) {
            foreach ($flowList as $flow) {
                $this->removeFlow($userId, $flow["id"]);
            }
        }
    }

    // 按日期统计收入支出
    public function getCountByTimeRange($userId, $inOut, $startTime, $endTime, $tagIds = "", $categoryId = "")
    {
        $startTime = date("Y-m-d H:i:s", $startTime);
        $endTime = date("Y-m-d H:i:s", $endTime);
        $where = "";
        if ($tagIds) {
            if (strpos($tagIds, ",") != false) {
                $where .= " and m.tagId in ($tagIds)";
            } else {
                $where .= " and m.tagId=$tagIds";
            }
        }
        if ($categoryId) {
            $where .= " and f.flowType=$categoryId";
        }
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "select sum(f.moneyIn) as sumIn, sum(f.moneyOut) as sumOut from {$this->flowTableName} f " . "left join {$tagMappingTableName} m on m.flowId=f.id " . "where f.userId={$userId} and f.inOrOut={$inOut} " . "and f.tradeTime>='{$startTime}' and f.tradeTime<'{$endTime}' " . $where;
        $row = $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
        return $row["sumIn"] - $row["sumOut"];
    }

    // 按日期统计收入支出
    public function getCountByTimeRangeExcludeFilter($userId, $inOut, $startTime, $endTime, $tagIds = "", $categoryIds = "", $excludeMarks="")
    {
        $startTime = date("Y-m-d H:i:s", $startTime);
        $endTime = date("Y-m-d H:i:s", $endTime);
        $where = "";
        if ($tagIds) {
            if (strpos($tagIds, ",") != false) {
                $where .= " and (m.tagId is null or m.tagId not in ($tagIds))";
            } else {
                $where .= " and (m.tagId is null or m.tagId <> $tagIds)";
            }
        }
        if ($categoryIds) {
            if (strpos($categoryIds, ",") != false) {
                $where .= " and f.flowType not in ($categoryIds)";
            } else {
                $where .= " and f.flowType <> $categoryIds";
            }
        }
        if ($excludeMarks) {
            $marks = explode(",", $excludeMarks);
            foreach($marks as $mark){
                $where .= " and f.desc not like '%$mark%'";
            }
        }
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "select sum(f.moneyIn) as sumIn, sum(f.moneyOut) as sumOut from {$this->flowTableName} f " . "left join {$tagMappingTableName} m on m.flowId=f.id " . "where f.userId={$userId} and f.inOrOut={$inOut} " . "and f.tradeTime>='{$startTime}' and f.tradeTime<'{$endTime}' " . $where;
        $row = $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
        return $row["sumIn"] - $row["sumOut"];
    }

    // 按日期查询收入支出
    public function findFlowsByTimeRangeExcludeFilter($userId, $inOut, $startTime, $endTime, $tagIds = "", $categoryIds = "", $excludeMarks="")
    {
        $startTime = date("Y-m-d H:i:s", $startTime);
        $endTime = date("Y-m-d H:i:s", $endTime);
        $where = "";
        if ($tagIds) {
            if (strpos($tagIds, ",") != false) {
                $where .= " and (m.tagId is null or m.tagId not in ($tagIds))";
            } else {
                $where .= " and (m.tagId is null or m.tagId <> $tagIds)";
            }
        }
        if ($categoryIds) {
            if (strpos($categoryIds, ",") != false) {
                $where .= " and f.flowType not in ($categoryIds)";
            } else {
                $where .= " and f.flowType <> $categoryIds";
            }
        }
        if ($excludeMarks) {
            $marks = explode(",", $excludeMarks);
            foreach($marks as $mark){
                $where .= " and f.desc not like '%$mark%'";
            }
        }
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "select f.*, (f.moneyIn+f.moneyOut) as money from {$this->flowTableName} f " . "left join {$tagMappingTableName} m on m.flowId=f.id " . "where f.userId={$userId} and f.inOrOut={$inOut} " . "and f.tradeTime>='{$startTime}' and f.tradeTime<'{$endTime}' " . $where . "order by money desc";
        return $this->db(ext_dbHandle::$slaveDbServer)->getRows($sql);
    }

    // 按日期统计收入支出并按类型分组
    public function getCountByTimeRangeGroupByType($userId, $inOut, $startTime, $endTime, $tagIds = "", $categoryId = "")
    {
        $startTime = date("Y-m-d H:i:s", $startTime);
        $endTime = date("Y-m-d H:i:s", $endTime);
        $where = "";
        if ($tagIds) {
            if (strpos($tagIds, ",") != false) {
                $where .= " and m.tagId in ($tagIds)";
            } else {
                $where .= " and m.tagId=$tagIds";
            }
        }
        if ($categoryId) {
            $where .= " and f.flowType=$categoryId";
        }
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "select tb1.*, f.name from (select f.flowType, sum(f.moneyIn) as sumIn, sum(f.moneyOut) as sumOut from {$this->flowTableName} f " . "left join {$tagMappingTableName} m on m.flowId=f.id " . "where f.userId={$userId} and f.inOrOut={$inOut} " . "and f.tradeTime>='{$startTime}' and f.tradeTime<'{$endTime}' " . $where . " group by f.flowType) as tb1 left join caakee_flowType as f on f.id=tb1.flowType order by sumOut desc, sumIn desc";
        return $this->db(ext_dbHandle::$slaveDbServer)->getRows($sql);
    }

    // 按日期统计收入支出并按账户分组
    public function getCountByTimeRangeGroupByAccount($userId, $inOut, $startTime, $endTime, $tagIds = "", $categoryId = "")
    {
        $startTime = date("Y-m-d H:i:s", $startTime);
        $endTime = date("Y-m-d H:i:s", $endTime);
        $where = "";
        if ($tagIds) {
            if (strpos($tagIds, ",") != false) {
                $where .= " and m.tagId in ($tagIds)";
            } else {
                $where .= " and m.tagId=$tagIds";
            }
        }
        if ($categoryId) {
            $where .= " and f.flowType=$categoryId";
        }
        $tagMappingTableName = tally_tag::$tagMappingTableName;
        $sql = "select tb1.*, f.name from (select f.accountId, sum(f.moneyIn) as sumIn, sum(f.moneyOut) as sumOut from {$this->flowTableName} f " . "left join {$tagMappingTableName} m on m.flowId=f.id " . "where f.userId={$userId} and f.inOrOut={$inOut} " . "and f.tradeTime>='{$startTime}' and f.tradeTime<'{$endTime}' " . $where . " group by f.accountId) as tb1 left join caakee_account as f on f.id=tb1.accountId";
        return $this->db(ext_dbHandle::$slaveDbServer)->getRows($sql);
    }

    public function getEndBalanceByTimeRange($userId, $endTime)
    {
        $endTime = date("Y-m-d H:i:s", $endTime);
        $balance = 0;
        $accountClass = new tally_account();
        $accountList = $accountClass->findAccountList($userId);
        foreach ($accountList as $account) {
          if ($account["accountType"] == 0 && $account["isShow"] == 1) {
            $sql = "select `remain` from `{$this->flowTableName}` f where f.userId={$userId} and f.accountId={$account["id"]} and f.tradeTime<'{$endTime}' order by f.tradeTime desc, f.id desc limit 0, 1";
  //            echo $sql."<br />\n";
            $row = $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
            if ($row) {
  //                echo $account["id"]." ".$account["name"]." ".$row["remain"]."<br/>";
              $balance += $row["remain"];

              // 对比是否与account.currentValue不一致
              if(0){
                $sql = "select * from `caakee_account`  where id={$account["id"]} ";
                //            echo $sql."<br />\n";
                $row1 = $this->db(ext_dbHandle::$slaveDbServer)->getRow($sql);
                if ($row1["currentValue"] != $row["remain"]) {
                  echo $account["id"] . " " . $account["name"] . " " . $row["remain"] . " " . $row1["currentValue"] . "<br/>\n";
                }
              }
            }
          }
        }

        return $balance;
    }
}
